# !/usr/bin/env python
# -*-coding:utf-8 -*-
# Project_name:XmidToExcelAndImportToTAPD
# File_name:xmid_to_excel_and_import_to_TAPD_no_window.py
# Author:chen chang song
# Time:2025年05月15日

import os
import re
import time
import json
import xmindparser
from openpyxl import Workbook
from openpyxl.styles import Alignment
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule
from openpyxl.worksheet.datavalidation import DataValidation


class KmToExcelConverter:
    def __init__(self, mind_path):
        """初始化转换器类
        :param mind_path: xmind文件存放目录路径
        """
        self.mind_path = mind_path

    def all_xmind_path_list(self):
        """遍历目录获取所有xmind文件，获取所有节点内容，写入到excel
        :return: 文解析后的数据列表
        """
        file_name_list = []
        xmind_data_list = []
        try:
            for filename in os.listdir(self.mind_path):
                if filename.endswith('.xmind'):
                    file_name_list.append(filename)
                    xmind_path = os.path.join(self.mind_path, filename)
                    try:
                        xmind_data = self.extract_xmind_content(xmind_path)
                        xmind_data_list.append(xmind_data)
                        self.test_case_write_to_excel(xmind_path, xmind_data)
                    except Exception as e:
                        print(f"解析 {xmind_path} 时出错: {e}")
        except FileNotFoundError:
            print(f"指定的目录 {self.mind_path} 未找到。")
        return xmind_data_list

    def extract_xmind_content(self, file_path):
        """解析xmind文件内容
        :param file_path: xmind文件路径
        :return: 提取的结构化测试用例数据
        """
        try:
            xmind_content = xmindparser.xmind_to_dict(file_path)
            # 提取 topic 下的 title 字段
            json_str = xmind_content[0]['topic']['title']
            # 需求id
            if '需求ID' in json_str:
                pattern = r'需求ID[：:]\s*(\d+)'
                match = re.search(pattern, json_str)
                requirement_id = match.group(1) if match else None
                print(f'需求ID：{requirement_id}')
            elif 'requirement_id' in json_str:
                data_list = json.loads(json_str)
                requirement_id = data_list['requirement_id']
                print(f'需求ID：{requirement_id}')
            else:
                requirement_id = ''
                print('需求ID格式不正确,请手动填写')

            results = []  # 存储提取结果

            def traverse(node, current_path, module_name):
                """递归遍历xmind节点
                :param node: 当前节点
                :param current_path: 当前路径列表
                :param module_name: 所属模块名
                """
                if 'topics' in node:
                    for topic in node['topics']:
                        new_path = current_path + [topic['title']]
                        if 'topics' in topic:
                            traverse(topic, new_path, module_name)
                        else:
                            # 提取叶子节点作为测试用例[需求ID,用例目录,用例名称,用例步骤,预期结果]
                            case_directory = module_name + "-" + "-".join(new_path[:-2]) if len(new_path) > 1 else ""
                            case_name = new_path[-2] + "-" + new_path[-1]
                            case_steps = " >> ".join(new_path[:-1]) if len(new_path) > 1 else ""
                            one_case = [requirement_id,case_directory, case_name,case_steps, new_path[-1]]
                            print(one_case)
                            results.append(one_case)


            # 处理每个模块
            for module in xmind_content[0]['topic']['topics']:
                module_name = module['title']
                if 'topics' in module:
                    for func in module['topics']:
                        traverse(func, [func['title']], module_name)

            return results
        except Exception as e:
            print(f"解析 {file_path} 时出错: {e}")
            return []

    def test_case_write_to_excel(self, xmind_path, row_data_list):
        """将测试用例数据写入Excel文件
        :param xmind_path: 原始xmind文件路径（用于生成输出文件名）
        :param row_data_list: 测试用例数据列表
        """
        # ============== 文件准备 ==============
        file_dir, file_name = os.path.split(xmind_path)
        file_base = os.path.splitext(file_name)[0]

        wb = Workbook()
        ws = wb.active
        ws.title = file_base  # 设置工作表名称

        # ============== 表头设置 ==============
        title_list = ['需求ID', '用例目录', '用例名称', '用例步骤', '预期结果', '执行结果', '备注']
        ws.append(title_list)

        # 设置自动筛选（范围A1-E1列）
        ws.auto_filter.ref = "A1:G1"

        # ============== 列宽设置 ==============
        col_config = {
            'A': 8,  # 需求ID
            'B': 40,  # 用例目录
            'C': 25,  # 用例名称
            'D': 80,  # 用例步骤
            'E': 20,  # 预期结果
            'F': 10,  # 执行结果
            'G': 25  # 备注
        }
        for col, width in col_config.items():
            ws.column_dimensions[col].width = width

        # ============== 数据验证设置 ==============
        status_options = ["通过", "失败", "阻塞", "未执行", "待确认", "用例待更新", "用例废弃"]
        dv = DataValidation(
            type="list",
            formula1=f'"{",".join(status_options)}"',
            allow_blank=True
        )
        ws.add_data_validation(dv)
        dv.add(f"F2:F{len(row_data_list) + 1}")  # 应用数据验证到F列

        # ============== 条件格式设置 ==============
        status_colors = {
            "通过": '00FF00',  # 绿色
            "失败": 'FF7F00',  # 橙色
            "阻塞": 'FF0000',  # 红色
            "未执行": 'FFFF00',  # 黄色
            "待确认": 'FFFF00',
            "用例待更新": 'FFFF00',
            "用例废弃": 'FFFF00'
        }
        for status, color in status_colors.items():
            rule = CellIsRule(
                operator='equal',
                formula=[f'"{status}"'],
                fill=PatternFill(
                    start_color=color,
                    end_color=color,
                    fill_type='solid'
                )
            )
            ws.conditional_formatting.add(f'F2:F{len(row_data_list) + 1}', rule)

        # ============== 写入测试数据 ==============
        for case_data in row_data_list:
            print(case_data)
            ws.append(case_data)

        # ============== 格式优化 ==============
        # 冻结首行
        ws.freeze_panes = 'A2'

        # 自动换行设置
        for row in ws.iter_rows(min_row=1):  # 包含标题行
            for cell in row:
                cell.alignment = Alignment(
                    wrap_text=True,
                    vertical='top'
                )

        # 行高设置
        ws.row_dimensions[1].height = 20  # 标题行
        for row_idx in range(2, ws.max_row + 1):
            ws.row_dimensions[row_idx].height = 32  # 数据行

        # ============== 文件保存 ==============
        timestamp = time.strftime("%Y%m%d%H%M%S")
        output_path = os.path.join(file_dir,f"{file_base}_{timestamp}.xlsx")
        try:
            wb.save(output_path)
        except Exception as e:
            print(f"保存文件 {output_path} 时出错: {e}")


if __name__ == "__main__":
    mind_path = r'..\file'
    converter = KmToExcelConverter(mind_path)
    test_cases = converter.all_xmind_path_list()
    print(f"共转换 {len(test_cases)} 个xmind文件")